using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using UMEVOR.DTO;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace UMEVOR.DAL
{
    public class DALT_HIST_ARTIGO : DALConnection
    {

        #region Incluir

        public void incluir(T_HIST_ARTIGO obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" INSERT INTO T_HIST_ARTIGO ");
                SQL.Append("    (ID_ARTIGO, DT_ACESSO) ");
                SQL.Append(" VALUES ");
                SQL.Append("    (" + obj.ID_ARTIGO.ToString() + ",str_to_date('" + obj.DT_ACESSO.ToString("dd/MM/yyyy HH:mm:ss") + "', '%d/%m/%Y %H:%i:%s'))");

                DbCommand insertT_HIST_ARTIGO = database.GetSqlStringCommand(SQL.ToString());

                insertT_HIST_ARTIGO.CommandText = SQL.ToString();

                database.ExecuteNonQuery(insertT_HIST_ARTIGO, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Incluir[]

        public void incluir(T_HIST_ARTIGO[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] insertT_HIST_ARTIGO = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_HIST_ARTIGO o = obj[index];
                    SQL = new StringBuilder();

                    SQL.Append(" INSERT INTO T_HIST_ARTIGO ");
                    SQL.Append("    (ID_ARTIGO, DT_ACESSO) ");
                    SQL.Append(" VALUES ");
                    SQL.Append("    (:ID_ARTIGO, :DT_ACESSO) ");

                    insertT_HIST_ARTIGO[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(insertT_HIST_ARTIGO[index], ":ID_ARTIGO", DbType.Int64, o.ID_ARTIGO);
                    database.AddInParameter(insertT_HIST_ARTIGO[index], ":DT_ACESSO", DbType.Object, o.DT_ACESSO);

                    insertT_HIST_ARTIGO[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in insertT_HIST_ARTIGO)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar

        public void alterar(T_HIST_ARTIGO obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" UPDATE T_HIST_ARTIGO SET ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_ARTIGO = :ID_ARTIGO  AND DT_ACESSO = :DT_ACESSO  ");

                DbCommand updateT_HIST_ARTIGO = database.GetSqlStringCommand(SQL.ToString());

                database.AddInParameter(updateT_HIST_ARTIGO, ":ID_ARTIGO", DbType.Int64, obj.ID_ARTIGO);
                database.AddInParameter(updateT_HIST_ARTIGO, ":DT_ACESSO", DbType.Object, obj.DT_ACESSO);

                updateT_HIST_ARTIGO.CommandText = SQL.ToString();

                database.ExecuteNonQuery(updateT_HIST_ARTIGO, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar[]

        public void alterar(T_HIST_ARTIGO[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] updateT_HIST_ARTIGO = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_HIST_ARTIGO o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" UPDATE T_HIST_ARTIGO SET ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_ARTIGO = :ID_ARTIGO  AND DT_ACESSO = :DT_ACESSO  ");

                    updateT_HIST_ARTIGO[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(updateT_HIST_ARTIGO[index], ":ID_ARTIGO", DbType.Int64, o.ID_ARTIGO);
                    database.AddInParameter(updateT_HIST_ARTIGO[index], ":DT_ACESSO", DbType.Object, o.DT_ACESSO);

                    updateT_HIST_ARTIGO[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in updateT_HIST_ARTIGO)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir

        public void excluir(T_HIST_ARTIGO obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" DELETE FROM T_HIST_ARTIGO ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_ARTIGO = :ID_ARTIGO  AND DT_ACESSO = :DT_ACESSO  ");

                DbCommand deleteT_HIST_ARTIGO = database.GetSqlStringCommand(SQL.ToString());

                database.AddInParameter(deleteT_HIST_ARTIGO, ":ID_ARTIGO", DbType.Int64, obj.ID_ARTIGO);
                database.AddInParameter(deleteT_HIST_ARTIGO, ":DT_ACESSO", DbType.Object, obj.DT_ACESSO);

                deleteT_HIST_ARTIGO.CommandText = SQL.ToString();

                database.ExecuteNonQuery(deleteT_HIST_ARTIGO, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir[]

        public void excluir(T_HIST_ARTIGO[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] deleteT_HIST_ARTIGO = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_HIST_ARTIGO o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" DELETE FROM T_HIST_ARTIGO ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_ARTIGO = :ID_ARTIGO  AND DT_ACESSO = :DT_ACESSO  ");

                    deleteT_HIST_ARTIGO[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(deleteT_HIST_ARTIGO[index], ":ID_ARTIGO", DbType.Int64, o.ID_ARTIGO);
                    database.AddInParameter(deleteT_HIST_ARTIGO[index], ":DT_ACESSO", DbType.Object, o.DT_ACESSO);

                    deleteT_HIST_ARTIGO[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in deleteT_HIST_ARTIGO)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ObterPorPK

        public T_HIST_ARTIGO obterPorPK(long pID_ARTIGO, object pDT_ACESSO)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_HIST_ARTIGO ");
                SQL.Append(" WHERE ");
                SQL.Append("  ID_ARTIGO = :ID_ARTIGO  AND DT_ACESSO = :DT_ACESSO  ");

                DbCommand consultaT_HIST_ARTIGO = database.GetSqlStringCommand(SQL.ToString());
                consultaT_HIST_ARTIGO.CommandText = SQL.ToString();

                database.AddInParameter(consultaT_HIST_ARTIGO, ":ID_ARTIGO", DbType.Int64, pID_ARTIGO);
                database.AddInParameter(consultaT_HIST_ARTIGO, ":DT_ACESSO", DbType.Object, pDT_ACESSO);

                IDataReader reader = database.ExecuteReader(consultaT_HIST_ARTIGO, DALConnection.transaction);
                T_HIST_ARTIGO obj = null;

                if (reader.Read())
                {
                    obj = new T_HIST_ARTIGO();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_ARTIGO")))
                    {
                        obj.ID_ARTIGO = Int64.Parse(reader["ID_ARTIGO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_ACESSO")))
                    {
                        obj.DT_ACESSO = DateTime.Parse(reader["DT_ACESSO"].ToString());
                    }
                }
                reader.Dispose();
                consultaT_HIST_ARTIGO.Dispose();

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ListarTodos

        public List<T_HIST_ARTIGO> listarTodos()
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_HIST_ARTIGO ");

                DbCommand consultaT_HIST_ARTIGO = database.GetSqlStringCommand(SQL.ToString());
                consultaT_HIST_ARTIGO.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_HIST_ARTIGO, DALConnection.transaction);
                List<T_HIST_ARTIGO> lista = new List<T_HIST_ARTIGO>();
                T_HIST_ARTIGO obj = null;

                while (reader.Read())
                {
                    obj = new T_HIST_ARTIGO();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_ARTIGO")))
                    {
                        obj.ID_ARTIGO = Int64.Parse(reader["ID_ARTIGO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_ACESSO")))
                    {
                        obj.DT_ACESSO = DateTime.Parse(reader["DT_ACESSO"].ToString());
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_HIST_ARTIGO.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<VOT_HIST_ARTIGO> consultar(DateTime inicio, DateTime fim)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append("SELECT A.TX_TITULO_ARTIGO AS TX_ARTIGO, COUNT(A.ID_ARTIGO) AS QT_ARTIGO FROM T_ARTIGO A, T_HIST_ARTIGO H ");
                SQL.Append("WHERE A.ID_ARTIGO = H.ID_ARTIGO AND H.DT_ACESSO BETWEEN str_to_date('" + inicio.ToString("dd/MM/yyy HH:mm") + "','%d/%m/%Y %H:%i') AND ");
                SQL.Append("str_to_date('" + fim.ToString("dd/MM/yyy HH:mm") + "','%d/%m/%Y %H:%i') GROUP BY A.TX_TITULO_ARTIGO ORDER BY A.TX_TITULO_ARTIGO");

                DbCommand consultaT_HIST_ARTIGO = database.GetSqlStringCommand(SQL.ToString());
                consultaT_HIST_ARTIGO.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_HIST_ARTIGO, DALConnection.transaction);
                List<VOT_HIST_ARTIGO> lista = new List<VOT_HIST_ARTIGO>();
                VOT_HIST_ARTIGO obj = null;

                while (reader.Read())
                {
                    obj = new VOT_HIST_ARTIGO();
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_ARTIGO")))
                    {
                        obj.TX_ARTIGO = reader["TX_ARTIGO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("QT_ARTIGO")))
                    {
                        obj.QT_ARTIGO = Int32.Parse(reader["QT_ARTIGO"].ToString());
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_HIST_ARTIGO.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }
}

